package com.ggkt.module.organizationalStruture.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ggkt.module.organizationalStruture.domain.DingtalkUsers;
import com.ggkt.module.organizationalStruture.domain.DtDept;
import com.ggkt.module.organizationalStruture.domain.WorkStatusStatistics;
import com.ggkt.module.organizationalStruture.domain.WorkTimeStatistics;
import org.apache.ibatis.annotations.Select;
import java.util.List;

/**
 * @author ggkt
 *
 */
public interface OrganizationalStructureDao extends BaseMapper<DtDept> {

    /**部门查询**/
    @Select("select * from dingtalk_dept where logicStatus=1 and parentid=#{parentId} and corpid=#{corpId}")
    List<DtDept> deptList(Integer parentId, String corpId)throws Exception;


    /**根据部门Id查询领导或员工信息**/
    @Select("select maindeptid,name,jobnumber,userId,position from dingtalk_users  t\n" +
            "where t.mainDeptId=#{mainDeptId} and corpId=#{corpId}")
    List<DingtalkUsers> userList(Integer mainDeptId, String corpId)throws Exception;

    /**查询部门下的小组**/
    @Select("select a.name,a.mainDeptId,b.orgName,b.orgId from dingtalk_users a" +
            " left join dingtalk_dept b on a.mainDeptId=b.parentId"+
            " where a.userId = #{userId} and a.corpId=#{corpId}")
    List<DingtalkUsers> deptListByUserId(Integer userId,String corpId)throws Exception;

    /**工龄统计**/
    @Select("select worktime,count(worktime) count from (\n" +
            "select a.name,unix_timestamp(now())*1000,a.hiredDate,(unix_timestamp(now())*1000-a.hiredDate)/1000/86400/365,\n" +
            "case\n" +
            "when (unix_timestamp(now())*1000-a.hiredDate)/1000/86400/365 between 0 and 1\n" +
            "then '0-1年'\n" +
            "when (unix_timestamp(now())*1000-a.hiredDate)/1000/86400/365 between 1 and 3\n" +
            "then '1-3年'\n" +
            "when (unix_timestamp(now())*1000-a.hiredDate)/1000/86400/365 between 3 and 5\n" +
            "then '3-5年'\n" +
            "when (unix_timestamp(now())*1000-a.hiredDate)/1000/86400/365 between 5 and 10\n" +
            "then '5-10年'\n" +
            "when (unix_timestamp(now())*1000-a.hiredDate)/1000/86400/365>10\n" +
            "then '10年以上'\n" +
            "ELSE '异常'\n" +
            "end worktime\n" +
            "from dingtalk_users a inner join dingtalk_dept b on a.mainDeptId=b.orgId\n" +
            "where a.corpId=#{corpId} and (b.orgId=#{deptId} or b.parentId=#{deptId})  ) t\n" +
            "group by worktime " )
    List<WorkTimeStatistics> workTimeList(String corpId, Integer deptId)throws Exception;

    /**待返岗用户**/
//    @Select("select count(*)\n" +
//            "from dingtalk_process_instance a\n" +
//            "inner join ats_dingtalk_leave b on a.processInstanceId=b.processInstanceId\n" +
//            "inner join dingtalk_users c on c.userId=a.originatorUserId\n" +
//            "inner join dingtalk_dept d on d.orgId=c.mainDeptId \n" +
//            "where b.endTime>now() and a.corpId=#{deptId}" +
//            "and (d.orgId=#{deptId} or d.parentId=#{corpId})")
//    Integer getLeaveUserNumberByOrgId(String corpId,Integer deptId)throws Exception;

    /**正式、试用期、待返岗员工统计**/
    @Select(
            "select '待返岗' as label,count(*) count\n" +
            "from dingtalk_process_instance a\n" +
            "inner join ats_dingtalk_leave b on a.processInstanceId=b.processInstanceId\n" +
            "inner join dingtalk_users c on c.userId=a.originatorUserId\n" +
            "inner join dingtalk_dept d on d.orgId=c.mainDeptId \n" +
            "where b.endTime>now() and a.corpId=#{corpId}\n" +
            "and (d.orgId=#{deptId} or d.parentId=#{deptId})\n" +
            "UNION \n" +
            "select e.label,count(1) count \n" +
            "from dingtalk_users d \n" +
            "INNER JOIN dingtalk_users_roster e on d.userId=e.userId and e.corpId=d.corpId \n" +
            "inner join dingtalk_dept f on f.orgId=d.mainDeptId and f.corpId=d.corpId \n" +
            "where e.fieldCode='sys01-employeeStatus' \n" +
            "and d.corpId=#{corpId} \n" +
            "and f.orgId=#{deptId} or f.parentId=#{deptId} and e.fieldCode='sys01-employeeStatus' group by e.label")
    List<WorkStatusStatistics> getUserWorkStatusByOrgId(String corpId, Integer deptId)throws Exception;


    /**根据corpId查询公司下的所有人**/
    @Select("select *\n" +
            " from dingtalk_users \n" +
            "where corpid=#{corpId} and status=0 ")
    List<DingtalkUsers> getUsersByCorpId(String corpId)throws Exception;
}
